library(tidyr)
library(readxl)
library(stringr)


setwd("C:/Users/jonves/Dropbox/Papers/SSP-Instability-Opinion/replication_data/")

to_long <- function(df){
  found_years <- grepl("[0-9]{4}", names(df))
  names(df)[found_years] <- paste("year", names(df)[found_years], sep="")
  df <- gather(df, year, value, which(found_years))
  df$year <- gsub("year", "", df$year)
  df$value <- as.numeric(df$value)
  df$year <- as.numeric(df$year)
  
  found_history <- grepl("History", names(df))
  if(any(found_history)){
    names(df)[found_history] <- "scenario"
    df$model <- NA
  } else {
    df$model <- str_split(df$`Model - Scenario`, pattern=" - ", simplify=T)[,1]
    df$scenario <- str_split(df$`Model - Scenario`, pattern=" - ", simplify=T)[,2]
    df$`Model - Scenario` <- NULL
  }
  
  names(df) <- str_to_lower(names(df))
  
  df <- df[c("region", "year", "scenario", "model", "variable", "unit", "value")]
  return(df)
}

excel_sheets("data/SSPdata.xlsx")
gdp <- read_excel("data/SSPdata.xlsx", sheet="GDP, countries historical")
urbanization <- read_excel("data/SSPdata.xlsx", sheet="Urbanization, countries hist")
pop <- read_excel("data/SSPdata.xlsx", sheet="Population, historical countrie")
ssp_gdp <- read_excel("data/SSPdata.xlsx", sheet="GDP, countries")
ssp_pop <- read_excel("data/SSPdata.xlsx", sheet="Population, countries")
ssp_urbanization <- read_excel("data/SSPdata.xlsx", sheet="Urbanization, countries")
ssp_gdp_32 <- read_excel("data/SSPdata.xlsx", sheet="GDP, 32 regions")
ssp_pop_32 <- read_excel("data/SSPdata.xlsx", sheet="Population, 32 regions")


gdp_long <- to_long(gdp)
pop_long <- to_long(pop)
ssp_pop_long <- to_long(ssp_pop)
ssp_gdp_long <- to_long(ssp_gdp)
ssp_urbanization_long <- to_long(ssp_urbanization)

ssp_gdp_32_long <- to_long(ssp_gdp_32)
ssp_pop_32_long <- to_long(ssp_pop_32)

df_32 <- rbind(ssp_gdp_32_long, ssp_pop_32_long)

df <- rbind(gdp_long, pop_long, ssp_pop_long, ssp_gdp_long, ssp_urbanization_long)

saveRDS(df, file = "data/ssp_long.rds")
saveRDS(df_32, file = "data/ssp_32_long.rds")










